Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Using ROWID with RUN STORED-PROCEDURE and LOAD-RESULT-INTO

In the previous section, Example 3–23 assumes you migrated your database to MS SQL Server using the CREATE RECID FIELD option, as discussed in the "ROWID characteristics" section. Example 3-24 and all subsequent examples in this section show ROWID as being represented by the 4-byte integer value of the Progress_RECID column as opposed to some other unique single-component index designated in your database to be the Progress recid.

Note: If you used a different single-component index to load the ROWID of a temp-table, you would need to map the column accordingly, just as the example maps PROGRESS_RECID.

The RUN STORED-PROC command has no native awareness that the MSS Database Table is being queried for the result set(s) it generates. Therefore, to allow DataServer technology to convert the stored PROGRESS_RECID value into a native Progress ROWID value, the physical name of the target database table needs to be known. To achieve this bond, the temp-table that the stored procedure will populate must be associated with an OpenEdge ProDataSet object.

Example 3–24 shows a 4GL query filling the temp tables of a ProDataSet. It will be used as the baseline code which will be referenced throughout the remainder of this section.

DEFINE TEMP-TABLE ttCust LIKE Sports.Customer        FIELD tRecid AS INT        FIELD tRECID_ident AS INT. DEFINE DATASET dsCust FOR ttCust. DEFINE VAR phDataSet AS HANDLE NO-UNDO. DEFINE QUERY qCust FOR Customer. phDataSet=DATASET dsCust:HANDLE. DEFINE DATA-SOURCE dsCust FOR QUERY qCust. BUFFER ttCust:handle:ATTACH-DATA-SOURCE(DATA-SOURCE dsCust:HANDLE,?,?,?). QUERY qCust:QUERY-PREPARE (“for each customer”). DATASET dsCust:FILL(). FOR EACH ttCust:    DISPLAY ttCust.name ttCust.tRecid. END.

Example 3–24: 4GL Query filling a ProDataSet temp-table

Example 3–25 combines code from Example 3–23 and Example 3–24 by applying the results of the RUN STORED-PROC [LOAD-RESULT-INTO] technique, rather than a 4GL query, to fill the temp-table associated with a ProDataSet.

DEFINE VARIABLE rid-1 AS ROWID, DEFINE VARIABLE rid-2 AS ROWID. DEFINE TEMP-TABLE ttCust LIKE Sports.Customer        FIELD tRecid AS ROWID /* MUST BE CHANGED TO ROWID TYPE */       FIELD tRECID_ident AS INT. DEFINE VAR hSendSQL AS HANDLE EXTENT 1. hSendSQL[1]=TEMP-TABLE ttCust:HANDLE. DEFINE DATASET dsCust FOR ttCust. DEFINE VAR phDataSet AS HANDLE NO-UNDO. DEFINE QUERY qCust FOR Customer. phDataSet=DATASET dsCust:HANDLE. DEFINE DATA-SOURCE dsCust FOR QUERY qCust. BUFFER ttCust:handle:ATTACH-DATA-SOURCE(DATA-SOURCE dsCust:HANDLE,?,?,?). FIND FIRST Customer WHERE Customer.custnum=1 NO_LOCK. rid-1=ROWID(customer). /* populate the ttCust TempTable */ RUN STORED-PROC send-sql-statement    LOAD-RESULT-INTO hSendSQL (“select * from customer”). FIND FIRST ttCust WHERE ttCust.custnum=1 NO-LOCK. rid-2=ttCust.tRecid. If rid-1 <> rid 2 THEN MESSAGE “The same record but different ROWID’s”. IF rid-1=rid-2 THEN MESSAGE “Congratulations - we have the same ROWID’s”. MESSAGE string(ttCust.tRecid) VIEW-AS ALERT-BOX.

Example 3–25: Using the LOAD-RESULT-INTO technique to populate the underlying Temp-Table of a ProDataSet

Keep the following key points in mind as you review Example 3–25:

The TEMP-TABLE field that is mapped to the PROGRESS_RECID column should be changed from its standard definition of INTEGER to ROWID.

In Example 3–25, the result column location where PROGRESS_RECID is being returned has been named tRecid in the temp-table. The PROGRESS_RECID_IDENT has been renamed tRECID_ident. This renaming occurs in Example 3-26 because of the following line:

FIELD tRecid AS ROWID /* must be changed to ROWID type */ FIELD tRECID_ident AS INT.

The TEMPTABLE must be defined to the ProDataSet. The following line, another excerpted line of code from Example 3–25, shows this definition:

DEFINE DATASET dsCust FOR ttCust.

The technique, demonstrated in Example 3–25, does not change the default behavior of the ROWID function, but provides a mechanism for the ROWID value to be stored along side its corresponding result rows; therefore, using the ROWID value to access database rows is unconventional with respect to the normal, or more typical, association between ROWID and a database table row. The following code demonstrates this difference.

Default use of ROWID function on a record buffer, as excerpted from Example 3–25:

FIND FIRST Customer WHERE Customer.custnum=1 NO-LOCK. rid-1=ROWID(customer).

In contrast, the following code, another excerpt from Example 3–25, demonstrates an alternative use of the ROWID value with a temp-table:

FIND FIRST Customer WHERE Customer.custnum=1 NO-LOCK. rid-2=ttCust.tRecid.